package ut.data;
import ut.res.MessageType;
import ut.util.Cache;
import java.util.Date;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.NamingException;
public class Message{
	public static final int NEW = 0; // 新訊息
	public static final int CHECKED = 1; // 已讀取
	public static final int DELETED = -1; // 已刪除
	// 系統發送的訊息
	public static boolean createMessage(int receiver, String content, MessageType messageType){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			con.setAutoCommit(false);
			// 取得收件者的名稱與地址
			String receiverName, receiverAddress;
			boolean emailVerified, emailNotified;
			rs = stmt.executeQuery("select name,email,verify,notify from user where id=" + receiver);
			if(rs.next()){
				emailVerified = rs.getInt("verify") > 0;
				emailNotified = rs.getBoolean("notify");
				receiverName = rs.getString("name");
				receiverAddress = rs.getString("email");
			}else{
				return false;
			}
			// 紀錄訊息
			int result = stmt.executeUpdate("insert into message(sender,receiver,content,time) values(" + User.SYSTEM_ID + "," + receiver + ",'" + content.replace("'", "''") + "',now())");
			// 更新收件者的訊息數量
			if(result == 1){
				stmt.executeUpdate("update user set message=message+1 where id=" + receiver);
			}
			con.commit();
			// 清除收件者訊息數量快取、寄出電子郵件
			if(result == 1){
				Cache.removeMessageCount(receiver);
				if(emailVerified && emailNotified){ // 收件者有通過電子信箱驗證，且設定接收訊息，才寄出信件
					ut.service.Mail.sendNotifyEmail(receiverAddress, receiverName, messageType);
				}
				return true;
			}else{
				return false;
			}
		}catch(NamingException e){
			e.printStackTrace();
			return false;
		}catch(SQLException e){
			e.printStackTrace();
			if(con != null){
				try{
					con.rollback();
				}catch(SQLException ex){
					return false;
				}
			}
			return false;
		}finally{
			try{
				if(con != null){
					con.rollback();
					con.setAutoCommit(true);
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return false;
			}
		}
	}
	// 使用者發送的訊息 ( 此功能暫時不用 )
	/*
	public static boolean createMessage(User sender, int receiver, String content){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			con.setAutoCommit(false);
			// 取得收件者的名稱與地址
			String receiverName, receiverAddress;
			rs = stmt.executeQuery("select name,email from user where id=" + receiver);
			if(rs.next()){
				receiverName = rs.getString("name");
				receiverAddress = rs.getString("email");
			}else{
				return false;
			}
			// 紀錄訊息
			int result = stmt.executeUpdate("insert into message(sender,receiver,content,time) values(" + sender.id + "," + receiver + ",'" + content.replace("'", "''") + "',now())");
			// 更新收件者的訊息數量
			if(result == 1){
				stmt.executeUpdate("update user set message=message+1 where id=" + receiver);
			}
			con.commit();
			// 清除收件者訊息數量快取、寄出電子郵件
			if(result == 1){
				Cache.removeMessageCount(receiver);
				MessageType messageType = MessageType.getMessageType(MessageType.USER_MESSAGE);
				ut.service.Mail.sendAsynchronous(receiverAddress, messageType.subject,
					MessageType.getFilledContent(messageType, new String[]{receiverName, sender.name}));
				return true;
			}else{
				return false;
			}
		}catch(NamingException e){
			e.printStackTrace();
			return false;
		}catch(SQLException e){
			e.printStackTrace();
			if(con != null){
				try{
					con.rollback();
				}catch(SQLException ex){
					return false;
				}
			}
			return false;
		}finally{
			try{
				if(con != null){
					con.rollback();
					con.setAutoCommit(true);
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return false;
			}
		}
	}
	*/
	public static int getMaxMessagePage(int receiver, int pageSize){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery("select count(*) from message where receiver=" + receiver + " and (state=" + Message.NEW + " or state=" + Message.CHECKED + ")");
			rs.next();
			return ((rs.getInt("count(*)") - 1) / pageSize) + 1;
		}catch(NamingException e){
			e.printStackTrace();
			return -1;
		}catch(SQLException e){
			e.printStackTrace();
			return -1;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return -1;
			}
		}
	}
	public static Message[] getMessages(int receiver, int pageSize, int pageNumber){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{

			con = Database.getConnection();
			stmt = con.createStatement();

			Message[] messages = new Message[pageSize];
			rs = stmt.executeQuery("select id,sender,content,state,time from message where receiver=" + receiver + " and (state=" + Message.NEW + " or state=" + Message.CHECKED + ") order by time desc limit " + ((pageNumber-1)*pageSize) + "," + pageSize);
			int index = 0;
			while(rs.next()){
				messages[index] = new Message(rs.getLong("id"), rs.getInt("sender"), rs.getString("content"), rs.getInt("state"), rs.getTimestamp("time"));
				index++;
			}
			return messages;
		}catch(NamingException e){
			e.printStackTrace();
			return null;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return null;
			}
		}
	}
	// 取得新訊息的數量
	public static int getNewMessageCount(int receiver){
		Integer count;
		if((count = Cache.getMessageCount(receiver)) != null){
			return count.intValue();
		}
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery("select message from user where id=" + receiver);
			if(rs.next()){
				count = rs.getInt("message");
				Cache.cacheMessageCount(receiver, count);
				return count;
			}else{
				return 0;
			}
		}catch(NamingException e){
			e.printStackTrace();
			return -1;
		}catch(SQLException e){
			e.printStackTrace();
			return -1;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return -1;
			}
		}
	}
	// 標示全部已讀取
	public static boolean checkedMessages(int receiver){
		if(Database.executeTransaction(new String[]{
			"update message set state=" + Message.CHECKED + " where receiver=" + receiver + " and state=" + Message.NEW,
			"update user set message=0 where id=" + receiver
		})){
			Cache.cacheMessageCount(receiver, 0);
			return true;
		}else{
			return false;
		}
	}
	// 刪除訊息
	public static boolean deleteMessage(long id, int receiver){
		return (Database.executeUpdate("update message set state=" + Message.DELETED + " where id=" + id + " and receiver=" + receiver) == 1);
	}
	/* Instance Definition */
	public long id;
	public int sender;
	public String content;
	public int state;
	public Date time;
	public Message(long id, int sender, String content, int state, Date time){
		this.id = id;
		this.sender = sender;
		this.content = content;
		this.state = state;
		this.time = time;
	}
}